import os
import pandas as pd
from django.conf import settings
from myapp.models import ObservationRecord  # 确保模型名称匹配
from datetime import datetime
from django.template.loader import render_to_string
import calendar
from datetime import date


def generate_static_html(year):
    # 获取指定年份的数据
    observations = ObservationRecord.objects.filter(date__year=year)
    day_status = {obs.date: obs.data_status for obs in observations}

    calendar_data = []
    for month in range(1, 13):
        first_weekday, num_days = calendar.monthrange(year, month)
        month_data = [{'day': '', 'status': 'no_data'}] * first_weekday

        for day in range(1, num_days + 1):
            day_date = date(year, month, day)
            status = day_status.get(day_date, 'no_data')
            month_data.append({'day': day, 'status': status})

        month_data = [month_data[i:i + 7] for i in range(0, len(month_data), 7)]
        calendar_data.append({'month_name': calendar.month_name[month], 'month_data': month_data})

    current_year = date.today().year
    earliest_record = ObservationRecord.objects.order_by('date').first()
    start_year = earliest_record.date.year if earliest_record else current_year

    # 年份范围：最早到当前年份
    year_range = list(range(start_year, current_year + 1))

    # 渲染HTML模板
    context = {
        'calendar_data': calendar_data,
        'selected_year': year,
        'year_range': year_range,
    }
    html_content = render_to_string('myindex3.html', context)

    # 将内容保存为静态 HTML 文件
    output_path = os.path.join(settings.BASE_DIR, f'static/calendar_{year}.html')
    with open(output_path, 'w', encoding='utf-8') as static_file:
        static_file.write(html_content)

    print(f"静态页面生成完毕：{output_path}")


def import_latest_observation_data():
    # 获取数据文件目录
    data_dir = os.path.join(settings.BASE_DIR, 'myapp/db/')

    # 找到最新的xls文件
    latest_file = None
    latest_file_time = None

    for file_name in os.listdir(data_dir):
        if file_name.endswith('.xls'):
            file_path = os.path.join(data_dir, file_name)
            file_time = os.path.getmtime(file_path)  # 获取文件的最后修改时间

            # 判断是否为最新文件
            if latest_file is None or file_time > latest_file_time:
                latest_file = file_path
                latest_file_time = file_time

    # 检查是否找到文件
    if latest_file is None:
        print("未找到任何.xls文件")
        return

    print(f"导入文件: {latest_file}")

    # 读取最新的Excel文件
    df = pd.read_excel(latest_file, header=0, engine='xlrd')

    # 去除列名的前后空格
    df.columns = df.columns.str.strip()

    # 遍历每行数据并保存到数据库中
    for _, row in df.iterrows():
        # 提取日期并格式化
        observation_date = pd.to_datetime(row.get('日期', None), errors='coerce').date() if pd.notnull(
            row.get('日期', None)) else None

        # 检查日期是否存在，如果存在则跳过
        if observation_date and ObservationRecord.objects.filter(date=observation_date).exists():
            print(f"跳过已存在的数据: {observation_date}")
            continue

        # 创建ObservationRecord对象并填充字段
        observation_data = ObservationRecord(
            date=observation_date,
            data_status=row.get('是否有数据', 'N/A'),
            url=row.get('网址', ''),
            observation_date=observation_date,
            observer=row.get('观测人', '未知'),
            location=row.get('地点', '未知'),
            cloud_cover=row.get('云量', '未记录'),
            weather=row.get('天气情况', '未记录'),
            altitude=int(row.get('海拔', 0)) if pd.notnull(row.get('海拔', None)) else 0,
            observation_time=row.get('观测时间', '未记录'),
            coordinates=row.get('经纬度', '未记录'),
            number=int(row.get('number', 0)) if pd.notnull(row.get('number', None)) else 0,
            info=row.get('信息', '无详细信息')
        )

        # 保存到数据库
        observation_data.save()
        print(f"导入数据: {observation_date}")

    print("数据导入完成！")